package com.example.danangtravel;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import com.exam.danangtranel.filed.ATM;
import com.exam.danangtranel.filed.Contact;
import com.exam.danangtranel.filed.Hotel;
import com.exam.danangtranel.filed.Taxi;
import com.exam.danangtranel.filed.Tbshopping;
import com.exam.danangtranel.filed.Travel;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

public class DataAccess {

	// Table tbATM
	public static final String TABLE_ATM = "tbATM";
	public static final String ATM_ID = "ID";
	public static final String ATM_Name = "Name";
	public static final String ATM_Adrress = "Adrress";
	public static final String ATM_Longitude = "Longitude";
	public static final String ATM_Latitude = "Latitude";

	// Table tbTaxi
	public static final String TABLE_Taxi = "tbTaxi";
	public static final String Taxi_Name = "Name";
	public static final String Taxi_Phone = "Phone";
	public static final String Taxi_Serve = "Serve";

	// Table tbFoodies and Table tbHotel: Dung chung
	public static final String TABLE_Foodies = "tbFoodies";
	public static final String TABLE_Hotel = "tbHotel   ";
	public static final String COL_ID = "ID";
	public static final String COL_Name = "Name";
	public static final String COL_Adrress = "Adrress";
	public static final String COL_Phone1 = "Phone";
	public static final String COL_Information = "Information";
	public static final String COL_Type = "Type";
	public static final String COL_Image = "Image";
	public static final String COL_Rating = "Rating";
	public static final String COL_Longitude = "Longitude";
	public static final String COL_Latitude = "Latitude";

	// Table tbShopping
	public static final String TABLE_Shopping = "tbShopping";
	public static final String Shopping_ID = "ID";
	public static final String Shopping_Name = "Name";
	public static final String Shopping_Adrress = "Adrress";
	public static final String Shopping_Information = "Information";
	public static final String Shopping_Type = "Type";
	public static final String Shopping_Specialty = "Specialty";
	public static final String Shopping_Longitude = "Longitude";
	public static final String Shopping_Latitude = "Latitude";

	// Table tbTravelPoint
	public static final String TABLE_TravelPoint = "tbTravelPoint";
	public static final String TravelPoint_ID = "ID";
	public static final String TravelPoint_Name = "Name";
	public static final String TravelPoint_Adrress = "Address";
	public static final String TravelPoint_Information = "Information";
	public static final String TravelPoint_Image1 = "Image1";
	public static final String TravelPoint_Longitude = "Longitude";
	public static final String TravelPoint_Latitude = "Latitude";

	// Table tbLienHe
	public static final String TABLE_LienHe = "LienHe";
	public static final String LienHe_Name = "Name";
	public static final String LienHe_Mail = "Mail";
	public static final String LienHe_Phone = "Phone";

	// Database fields
	private SQLiteDatabase database;
	private MySQLiteHelper dbHelper;

	public ArrayList<String> listgroup, group;

	public DataAccess(Context context) throws IOException {
		try {
			dbHelper = new MySQLiteHelper(context);
			dbHelper.createDataBase();
		} catch (IOException ioe) {
			throw ioe;
		} catch (SQLException sqle) {
			throw sqle;
		}
		database = dbHelper.getReadableDatabase();

	}

	public void open() throws SQLException {
		dbHelper.openDataBase();
	}

	public void close() {
		dbHelper.close();
	}

	// code for table tbATM

	// code for table tbTaxi
	public ArrayList<Taxi> getAlTaxi() {
		String sql = "SELECT * FROM " + TABLE_Taxi;
		ArrayList<Taxi> listTaxi = new ArrayList<Taxi>();
		Cursor cursor = database.rawQuery(sql, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Taxi tbTaxi = new Taxi();
			tbTaxi.setName(cursor.getString(0));
			tbTaxi.setPhone(cursor.getString(1));
			tbTaxi.setServe(cursor.getString(2));
			listTaxi.add(tbTaxi);
			cursor.moveToNext();
		}
		cursor.close();
		return listTaxi;
	}

	// code for table LienHe
	public ArrayList<Contact> getAlContact() {
		String sql = "SELECT * FROM " + TABLE_LienHe;
		ArrayList<Contact> listLienHe = new ArrayList<Contact>();
		Cursor cursor = database.rawQuery(sql, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Contact LienHe = new Contact();
			LienHe.setName(cursor.getString(1));
			LienHe.setMail(cursor.getString(2));
			LienHe.setPhone(cursor.getString(3));
			listLienHe.add(LienHe);
			cursor.moveToNext();
		}
		cursor.close();
		return listLienHe;
	}

	// code for table tbFoodies

	// code for table tbHotel
	public ArrayList<Hotel> getAllHotel(String type) {
		String sql = "SELECT * FROM " + TABLE_Hotel + " WHERE Type = " + type;
		ArrayList<Hotel> list = new ArrayList<Hotel>();
		Cursor cursor = database.rawQuery(sql, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Hotel tbHotel = new Hotel();
			tbHotel.setID(cursor.getInt(0));
			tbHotel.setName(cursor.getString(1));
			tbHotel.setImage(cursor.getString(6));
			tbHotel.setRating(cursor.getInt(7));
			list.add(tbHotel);
			cursor.moveToNext();
		}
		cursor.close();
		return list;
	}

	public Hotel getObjHotel(int ID) {
		Hotel objHotel = new Hotel();
		String sql = "SELECT * FROM " + TABLE_Hotel + " WHERE ID = " + ID;
		Cursor cursor = database.rawQuery(sql, null);
		if (cursor != null)
			cursor.moveToFirst();
		objHotel.setName(cursor.getString(1));
		objHotel.setAdrress(cursor.getString(2));
		objHotel.setPhone(cursor.getString(3));
		objHotel.setInformation(cursor.getString(4));
		objHotel.setImage(cursor.getString(6));
		objHotel.setRating(cursor.getInt(7));
		objHotel.setLongitude(cursor.getString(8));
		objHotel.setLatitude(cursor.getString(9));
		cursor.close();
		return objHotel;
	}

	// ************************** code for table tbShopping
	public List<Tbshopping> getAllShopping(String title) {
		// Dung rawquery cho de~
		String sql = "SELECT * FROM " + TABLE_Shopping + " WHERE Type = "
				+ title;
		List<Tbshopping> list = new ArrayList<Tbshopping>();
		// Cursor cursor = database.query(TABLE_Shopping, allcolume_tbshopping,
		// null, null, null, null, null);
		Cursor cursor = database.rawQuery(sql, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {

			Tbshopping tbshoping = new Tbshopping();
			tbshoping.setAdress(cursor.getString(1)); // Name
			tbshoping.setName(cursor.getString(2)); // Address
			tbshoping.setLatitude(cursor.getString(4));
			tbshoping.setLongitude(cursor.getString(5));
			list.add(tbshoping);
			cursor.moveToNext();
		}
		cursor.close();
		return list;
	}

	// ********************************* end code for tabale tbShopping
	// code for table tbTravelPoint
	public ArrayList<Travel> getAlTravel(int id) {
		String sql = "SELECT * FROM " + TABLE_TravelPoint + " WHERE ID = " + id;
		ArrayList<Travel> list = new ArrayList<Travel>();
		Cursor cr = database.rawQuery(sql, null);
		cr.moveToFirst();
		while (!cr.isAfterLast()) {
			Travel travel = new Travel();
			travel.setID(cr.getInt(0));
			travel.setName(cr.getString(1));
			travel.setAddress(cr.getString(2));
			travel.setInformation(cr.getString(3));
			travel.setImage(cr.getString(4));
			list.add(travel);
			cr.moveToNext();
		}
		cr.close();
		return list;
	}

	// +++++++++++++++++RESTAURANT
	// +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	public List<Hotel> getAllFood(int Type) {
		// Dung rawquery cho de~
		String sql = "SELECT * FROM " + TABLE_Foodies + " WHERE Type = " + Type;
		List<Hotel> list = new ArrayList<Hotel>();
		// Cursor cursor = database.query(TABLE_Shopping, allcolume_tbshopping,
		// null, null, null, null, null);
		Cursor cursor = database.rawQuery(sql, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {

			Hotel tbhotel = new Hotel();
			tbhotel.setID(cursor.getInt(0));
			tbhotel.setAdrress(cursor.getString(1)); // Name
			tbhotel.setName(cursor.getString(2));// Address
			tbhotel.setImage(cursor.getString(6));
			tbhotel.setLongitude(cursor.getString(9));
			tbhotel.setLatitude(cursor.getString(10));
			tbhotel.setInformation(cursor.getString(4));

			list.add(tbhotel);
			cursor.moveToNext();
		}
		cursor.close();
		return list;
	}

	public Hotel getAllFood1(int id) {
		// Dung rawquery cho de~
		String sql = "SELECT * FROM " + TABLE_Foodies + " WHERE ID = " + id;
		Hotel tbhotel = new Hotel();
		// Cursor cursor = database.query(TABLE_Shopping, allcolume_tbshopping,
		// null, null, null, null, null);
		Cursor cursor = database.rawQuery(sql, null);
		if (cursor != null)
			cursor.moveToFirst();
		tbhotel.setID(cursor.getInt(0));
		tbhotel.setAdrress(cursor.getString(1)); // Name
		tbhotel.setName(cursor.getString(2));// Address
		tbhotel.setImage(cursor.getString(6));
		tbhotel.setLongitude(cursor.getString(9));
		tbhotel.setLatitude(cursor.getString(10));
		tbhotel.setInformation(cursor.getString(4));

		cursor.close();
		return tbhotel;
	}

	public Hotel getOntItemOfTableFoodies(int idOfTableFoodies) {
		Hotel dungchung = new Hotel();
		String sql = "SELECT * FROM " + TABLE_Foodies + " WHERE Type = 1";
		Cursor cursor = database.rawQuery(sql, null);
		cursor.move(idOfTableFoodies);
		dungchung.setName(cursor.getString(1));
		dungchung.setAdrress(cursor.getString(2));
		dungchung.setInformation(cursor.getString(4));
		dungchung.setImage(cursor.getString(6));
		cursor.close();
		return dungchung;
	}

	public List<Hotel> getAllFood_drink() {
		// Dung rawquery cho de~
		String sql = "SELECT * FROM " + TABLE_Foodies + " WHERE Type = 4";
		List<Hotel> list = new ArrayList<Hotel>();
		// Cursor cursor = database.query(TABLE_Shopping, allcolume_tbshopping,
		// null, null, null, null, null);
		Cursor cursor = database.rawQuery(sql, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {

			Hotel tbhotel = new Hotel();
			tbhotel.setName(cursor.getString(2));
			tbhotel.setAdrress(cursor.getString(1));
			list.add(tbhotel);
			cursor.moveToNext();
		}
		cursor.close();
		return list;
	}

	// End+++++++++++++++++RESTAURANT
	// +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	// ATM TEST
	public ArrayList<String> getATM() {
		listgroup = new ArrayList<String>();
		String sql = "SELECT " + ATM_Name + " FROM " + TABLE_ATM + " GROUP BY "
				+ ATM_Name;
		Cursor cursor = database.rawQuery(sql, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			listgroup.add(cursor.getString(0));
			cursor.moveToNext();
		}
		cursor.close();
		return listgroup;
	}

	public List<ATM> getChildATM(String name) {
		List<ATM> listatm = new ArrayList<ATM>();
		String sql = "SELECT * FROM " + DataAccess.TABLE_ATM + " WHERE "
				+ ATM_Name + " = \"" + name + "\" ";
		Cursor cursor = database.rawQuery(sql, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			ATM atm = new ATM();
			atm.setId(cursor.getInt(0));
			atm.setAddress(cursor.getString(2));
			atm.setName(cursor.getString(1));
			atm.setLatitude(cursor.getString(4));
			atm.setLongitude(cursor.getString(3));
			listatm.add(atm);
			cursor.moveToNext();
		}
		cursor.close();
		return listatm;
	}
	// END ATM
}
